import traceback

from flask import Blueprint, json, Response, request
from common.engines import get_engine
from common.utils.timer import FuncTimer
from config import config
import logging
from common.utils.extend_json_encoder import ExtendJSONEncoderFTime
from models.sql.sql_model import Instance, QueryLog
from extensions.init_sqlarchemy import db

logger = logging.getLogger("default")
sql_bp = Blueprint('sql', __name__, url_prefix='/sql')


def reg_sql_bp(app):
    app.register_blueprint(sql_bp)


@sql_bp.route('/')
def hello():
    return '<h1>hello sql</h1>'


@sql_bp.route("/query")
def query():
    """
    获取SQL查询结果
    :param request:
    :return:
    """
    instance_name = request.args.get("instance_name")
    sql_content = request.args.get("sql_content")
    db_name = request.args.get("db_name")
    # schema_name = ''

    result = {"status": 0, "msg": "ok", "data": {}}
    # try:
    #     instance = user_instances(request.user).get(instance_name=instance_name)
    # except Instance.DoesNotExist:
    #     result["status"] = 1
    #     result["msg"] = "你所在组未关联该实例"
    #     return Response(json.dumps(result), content_type="application/json")
    instance = db.session.query(Instance).filter_by(instance_name=instance_name).first()
    # 服务器端参数验证
    if None in [sql_content, db_name, instance_name]:
        result["status"] = 1
        result["msg"] = "页面提交参数可能为空"
        return Response(json.dumps(result))
    try:
        # config = SysConfig()
        # 查询前的检查，禁用语句检查，语句切分
        query_engine = get_engine(instance)
        query_check_info = query_engine.query_check(db_name=db_name, sql=sql_content)
        if query_check_info.get("bad_query"):
            # 引擎内部判断为 bad_query
            result["status"] = 1
            result["msg"] = query_check_info.get("msg")
            return Response(json.dumps(result), content_type="application/json")
        # sql_content = query_check_info["filtered_sql"]
        #
        # # 查询权限校验，并且获取limit_num
        # priv_check_info = query_priv_check(
        #     user, instance, db_name, sql_content, limit_num
        # )
        # if priv_check_info["status"] == 0:
        #     limit_num = priv_check_info["data"]["limit_num"]
        #     priv_check = priv_check_info["data"]["priv_check"]
        # else:
        #     result["status"] = priv_check_info["status"]
        #     result["msg"] = priv_check_info["msg"]
        #     return Response(json.dumps(result), content_type="application/json")
        # explain的limit_num设置为0
        # limit_num = 0 if re.match(r"^explain", sql_content.lower()) else limit_num

        # 对查询sql增加limit限制或者改写语句
        sql_content = query_engine.filter_sql(sql=sql_content)

        # 先获取查询连接，用于后面查询复用连接以及终止会话
        query_engine.get_connection(db_name=db_name)
        max_execution_time = int(config.DevelopmentConfig.max_execution_time)
        # 执行查询语句，并增加一个定时终止语句的schedule，timeout=max_execution_time

        with FuncTimer() as t:
            # 获取主从延迟信息
            query_result = query_engine.query(
                db_name,
                sql_content,
                max_execution_time=max_execution_time * 1000,
            )
        query_result.query_time = t.cost

        # 查询异常
        if query_result.error:
            result["status"] = 1
            result["msg"] = query_result.error
        else:
            result["data"] = query_result.__dict__

        # 仅将成功的查询语句记录存入数据库
        # if not query_result.error:
        #     result["data"]["seconds_behind_master"] = seconds_behind_master
        #     if int(limit_num) == 0:
        #         limit_num = int(query_result.affected_rows)
        #     else:
        #         limit_num = min(int(limit_num), int(query_result.affected_rows))
        #     query_log = QueryLog(
        #         username=user.username,
        #         user_display=user.display,
        #         db_name=db_name,
        #         instance_name=instance.instance_name,
        #         sqllog=sql_content,
        #         effect_row=limit_num,
        #         cost_time=query_result.query_time,
        #         priv_check=priv_check,
        #         hit_rule=query_result.mask_rule_hit,
        #         masking=query_result.is_masked,
        #     )
        #     # 防止查询超时
        #     if connection.connection and not connection.is_usable():
        #         close_old_connections()
        #     query_log.save()
    except Exception as e:
        logger.error(f"查询异常报错，查询语句：{sql_content}\n，错误信息：{traceback.format_exc()}")
        result["status"] = 1
        result["msg"] = f"查询异常报错，错误信息：{e}"
        return Response(json.dumps(result), content_type="application/json")
    # 返回查询结果
    try:
        return Response(json.dumps(
            result,
            use_decimal=False,
            cls=ExtendJSONEncoderFTime,
            bigint_as_string=True,
        ))
    # 虽然能正常返回，但是依然会乱码
    except UnicodeDecodeError:
        return Response(json.dumps(result, default=str, bigint_as_string=True, encoding="latin1"),
                        content_type="application/json")


@sql_bp.route('/query_database_list', methods=["GET"])
def query_database_list():
    """
    根据环境获取database
    :param :
    :return:
    """
    instance_name = request.args.get("instance_name")
    instance = db.session.query(Instance).filter_by(instance_name=instance_name).first()
    result = {"status": 0, "msg": "ok", "data": {}}
    try:
        query_engine = get_engine(instance)
        query_result = query_engine.get_all_databases()
        if query_result.error:
            result["status"] = 1
            result["msg"] = query_result.error
        else:
            result["data"] = query_result.rows
    except Exception as e:
        result["status"] = 1
        result["msg"] = e
    return Response(json.dumps(result))


@sql_bp.route('/query_table_list', methods=["GET"])
def query_table_list():
    """
    根据环境获取table
    :param instance_name:
    :return:
    """
    db_name = request.args.get("db_name")
    instance_name = request.args.get("instance_name")
    # db_name = 'rdc_manager'
    # instance_name = 'std'
    instance = db.session.query(Instance).filter_by(instance_name=instance_name).first()
    result = {"status": 0, "msg": "ok", "data": {}}
    try:
        query_engine = get_engine(instance)
        query_result = query_engine.get_all_tables(db_name)
        if query_result.error:
            result["status"] = 1
            result["msg"] = query_result.error
        else:
            result["data"] = query_result.rows
    except Exception as e:
        result["status"] = 1
        result["msg"] = e
    return Response(json.dumps(result))


@sql_bp.route('/query_env_list', methods=["GET"])
def query_env_list():
    """
    获取环境列表
    :return:
    """
    result = {"status": 0, "msg": "ok", "data": {}}
    try:
        instance = db.session.query(db.distinct(Instance.instance_name)).all()
        result["data"] = [i[0] for i in instance]
    except Exception as e:
        result["status"] = 1
        result["msg"] = e
    return Response(json.dumps(result))


@sql_bp.route('/record_log', methods=["POST"])
def record_query_log():
    """
    记录sql查询记录
    :return:
    """
    result = {"status": 0, "msg": "ok", "data": {}}
    data = request.json
    name = data.get('username')
    text = data.get('text')
    instance_name = data.get('instance_name')
    database = data.get('db_name')
    query_status = data.get('query_status')
    query_message = data.get('query_message')
    effect_row = data.get('effect_row')
    q_log = QueryLog(instance_name=instance_name, db_name=database, sqllog=text, effect_row=effect_row, username=name,
                     query_status=query_status, query_message=query_message)

    try:
        db.session.add(q_log)
        db.session.commit()
        return Response(json.dumps(result))
    except Exception as e:
        result["msg"] = e
        result["status"] = 1
        return Response(json.dumps(result))


if __name__ == '__main__':
    from app import app

    with app.app_context():
        # r = query('std', 'select * from user limit 1', 'rdc_manager')
        # r = query_table_list()
        # print(r.data.decode('UTF-8'))
        print(hello())
